Postgres Setup

Part 1, Chapter 5


In this chapter, we'll configure Postgres, get it up and running in another container, and link it to the web service. We'll also set up Tortoise ORM and configure a model.


Postgres

To configure Postgres, we'll need to add a new service to the docker-compose.yml file, add the appropriate environment variables, and install asyncpg.

Add a "db" directory to "project", and add a create.sql file in that new directory:

CREATE DATABASE web_dev;
CREATE DATABASE web_test;

Next, add a Dockerfile to the same directory:

# pull official base image
FROM postgres:14-alpine

# run create.sql on init
ADD create.sql /docker-entrypoint-initdb.d

Here, we extended the official Postgres image (an Alpine-based image) by adding create.sql to the "docker-entrypoint-initdb.d" directory in the container. This file will execute on init.

Next, add a new service called web-db to docker-compose.yml:

version: '3.8'

services:

  web:
    build: ./project
    command: uvicorn app.main:app --reload --workers 1 --host 0.0.0.0 --port 8000
    volumes:
      - ./project:/usr/src/app
    ports:
      - 8004:8000
    environment:
      - ENVIRONMENT=dev
      - TESTING=0
      - DATABASE_URL=postgres://postgres:postgres@web-db:5432/web_dev        # new
      - DATABASE_TEST_URL=postgres://postgres:postgres@web-db:5432/web_test  # new
    depends_on:   # new
      - web-db

  # new
  web-db:
    build:
      context: ./project/db
      dockerfile: Dockerfile
    expose:
      - 5432
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres

Once spun up, Postgres will be available on port 5432 for services running in other containers. Since the web service is dependent not only on the container being up and running but also the actual Postgres instance being up and healthy, let's add an entrypoint.sh file to the "project" directory:

#!/bin/sh

echo "Waiting for postgres..."

while ! nc -z web-db 5432; do
  sleep 0.1
done

echo "PostgreSQL started"

exec "$@"

So, we referenced the Postgres container using the name of the service, web-db. The loop continues until something like Connection to web-db port 5432 [tcp/postgresql] succeeded! is returned.

Update Dockerfile to install the appropriate packages and supply an entrypoint:

# pull official base image
FROM python:3.10.1-slim-buster

# set working directory
WORKDIR /usr/src/app

# set environment variables
ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1

# install system dependencies
RUN apt-get update \
  && apt-get -y install netcat gcc postgresql \
  && apt-get clean

# install python dependencies
RUN pip install --upgrade pip
COPY ./requirements.txt .
RUN pip install -r requirements.txt

# add app
COPY . .

# add entrypoint.sh
COPY ./entrypoint.sh .
RUN chmod +x /usr/src/app/entrypoint.sh

# run entrypoint.sh
ENTRYPOINT ["/usr/src/app/entrypoint.sh"]

Add asyncpg to project/requirements.txt:

asyncpg==0.25.0

Next, update config.py to read the DATABASE_URL and assign the value to database_url:

# project/app/config.py


import logging
import os
from functools import lru_cache

from pydantic import BaseSettings, AnyUrl


log = logging.getLogger("uvicorn")


class Settings(BaseSettings):
    environment: str = os.getenv("ENVIRONMENT", "dev")
    testing: bool = os.getenv("TESTING", 0)
    database_url: AnyUrl = os.environ.get("DATABASE_URL")


@lru_cache()
def get_settings() -> BaseSettings:
    log.info("Loading config settings from the environment...")
    return Settings()

Take note of the AnyUrl validator.

Build the new image and spin up the two containers:

$ chmod +x project/entrypoint.sh
$ docker-compose up -d --build

Once done, check the logs for the web service:

$ docker-compose logs web

You should see:

web_1  | Waiting for postgres...
web_1  | PostgreSQL started
web_1  | INFO:     Will watch for changes in these directories: ['/usr/src/app']
web_1  | INFO:     Uvicorn running on http://0.0.0.0:8000 (Press CTRL+C to quit)
web_1  | INFO:     Started reloader process [1] using statreload
web_1  | INFO:     Started server process [37]
web_1  | INFO:     Waiting for application startup.
web_1  | INFO:     Application startup complete.

Depending on your environment, you may need to chmod 755 or 777 instead of +x. If you still get a "permission denied", review the docker entrypoint running bash script gets "permission denied" Stack Overflow question.

Want to access the database via psql?

$ docker-compose exec web-db psql -U postgres

Then, you can connect to the database:

postgres=# \c web_dev
postgres=# \q

Tortoise ORM

To simplify interactions with the database, we'll using an async ORM called Tortoise.

Add the dependency to the requirements.txt file:

tortoise-orm==0.17.8

Next, create a new folder called "models" within "project/app". Add two files to "models": __init__.py and tortoise.py.

In tortoise.py, add:

# project/app/models/tortoise.py


from tortoise import fields, models


class TextSummary(models.Model):
    url = fields.TextField()
    summary = fields.TextField()
    created_at = fields.DatetimeField(auto_now_add=True)

    def __str__(self):
        return self.url

Here, we defined a new database model called TextSummary.

Add the register_tortoise helper to main.py to set up Tortoise on startup and clean up on teardown:

# project/app/main.py

import os

from fastapi import FastAPI, Depends
from tortoise.contrib.fastapi import register_tortoise

from app.config import get_settings, Settings


app = FastAPI()


register_tortoise(
    app,
    db_url=os.environ.get("DATABASE_URL"),
    modules={"models": ["app.models.tortoise"]},
    generate_schemas=True,
    add_exception_handlers=True,
)


@app.get("/ping")
async def pong(settings: Settings = Depends(get_settings)):
    return {
        "ping": "pong!",
        "environment": settings.environment,
        "testing": settings.testing
    }

Sanity check:

$ docker-compose up -d --build

Ensure the textsummary table was created:

$ docker-compose exec web-db psql -U postgres

psql (14.1)
Type "help" for help.

postgres=# \c web_dev
You are now connected to database "web_dev" as user "postgres".

web_dev=# \dt
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | textsummary | table | postgres
(1 row)

web_dev=# \q

http://localhost:8004/ping should still work as well:

{
  "ping": "pong!",
  "environment": "dev",
  "testing": false
}

You should now have:

├── .gitignore
├── docker-compose.yml
└── project
    ├── .dockerignore
    ├── Dockerfile
    ├── app
    │   ├── __init__.py
    │   ├── config.py
    │   ├── main.py
    │   └── models
    │       ├── __init__.py
    │       └── tortoise.py
    ├── db
    │   ├── Dockerfile
    │   └── create.sql
    ├── entrypoint.sh
    └── requirements.txt

Migrations

Tortoise supports database migrations via Aerich. Let's take a few steps back and configure it.

First, bring down the containers and volumes to destroy the current database table since we want Aerich to manage the schema:

$ docker-compose down -v

Next, update the register_tortoise helper in project/app/main.py so that the schemas are not automatically generated:

register_tortoise(
    app,
    db_url=os.environ.get("DATABASE_URL"),
    modules={"models": ["app.models.tortoise"]},
    generate_schemas=False,  # updated
    add_exception_handlers=True,
)

Spin the containers and volumes back up:

$ docker-compose up -d --build

Ensure the textsummary table was NOT created:

$ docker-compose exec web-db psql -U postgres

psql (14.1)
Type "help" for help.

postgres=# \c web_dev
You are now connected to database "web_dev" as user "postgres".

web_dev=# \dt
Did not find any relations.

web_dev=# \q

Add Aerich to the requirements file:

aerich==0.6.1

Then, update the containers:

$ docker-compose up -d --build

Aerich requires the Tortoise config, so let's add that to a new file called project/app/db.py:

# project/app/db.py


import os


TORTOISE_ORM = {
    "connections": {"default": os.environ.get("DATABASE_URL")},
    "apps": {
        "models": {
            "models": ["app.models.tortoise", "aerich.models"],
            "default_connection": "default",
        },
    },
}

Now we're ready to start using Aerich.

Init:

$ docker-compose exec web aerich init -t app.db.TORTOISE_ORM

This will create a config file called project/pyproject.toml:

[tool.aerich]
tortoise_orm = "app.db.TORTOISE_ORM"
location = "./migrations"
src_folder = "./."

Create the first migration:

$ docker-compose exec web aerich init-db

Success create app migrate location migrations/models
Success generate schema for app "models"

You should see the following migration inside a new file within "migrations/models":

-- upgrade --
CREATE TABLE IF NOT EXISTS "textsummary" (
    "id" SERIAL NOT NULL PRIMARY KEY,
    "url" TEXT NOT NULL,
    "summary" TEXT NOT NULL,
    "created_at" TIMESTAMPTZ NOT NULL  DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS "aerich" (
    "id" SERIAL NOT NULL PRIMARY KEY,
    "version" VARCHAR(255) NOT NULL,
    "app" VARCHAR(20) NOT NULL,
    "content" JSONB NOT NULL
);

That's it! You should now be able to see the two tables:

$ docker-compose exec web-db psql -U postgres

psql (14.1)
Type "help" for help.

postgres=# \c web_dev
You are now connected to database "web_dev" as user "postgres".

web_dev=# \dt
            List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | aerich      | table | postgres
 public | textsummary | table | postgres
(2 rows)

web_dev=# \q

Be sure to review the documentation to learn more about the available commands. Take note of the downgrade, history, and upgrade commands.




✓ Mark as Completed